const { DataTypes } = require('sequelize');

/**
 * 根据API文档创建所有必需的数据库表
 * 包含轮播图、功能模块、活动、检测文本、游戏内容、每日一句、通知等表
 */
module.exports = {
  async up(queryInterface, Sequelize) {
    const transaction = await queryInterface.sequelize.transaction();
    
    try {
      console.log('🏗️  开始创建API相关数据库表...');

      // 1. 轮播图表
      await queryInterface.createTable('banners', {
        id: {
          type: DataTypes.INTEGER,
          primaryKey: true,
          autoIncrement: true,
          comment: '轮播图ID'
        },
        title: {
          type: DataTypes.STRING(200),
          allowNull: false,
          comment: '标题'
        },
        description: {
          type: DataTypes.TEXT,
          allowNull: true,
          comment: '描述'
        },
        image: {
          type: DataTypes.STRING(500),
          allowNull: false,
          comment: '图片URL'
        },
        link: {
          type: DataTypes.STRING(500),
          allowNull: true,
          comment: '跳转链接'
        },
        sort: {
          type: DataTypes.INTEGER,
          defaultValue: 0,
          comment: '排序'
        },
        status: {
          type: DataTypes.ENUM('active', 'inactive'),
          defaultValue: 'active',
          comment: '状态'
        },
        created_at: {
          type: DataTypes.DATE,
          allowNull: false,
          defaultValue: Sequelize.NOW
        },
        updated_at: {
          type: DataTypes.DATE,
          allowNull: false,
          defaultValue: Sequelize.NOW
        }
      }, {
        transaction,
        charset: 'utf8mb4',
        collate: 'utf8mb4_unicode_ci',
        comment: '轮播图表'
      });

      // 2. 功能模块表
      await queryInterface.createTable('function_modules', {
        id: {
          type: DataTypes.INTEGER,
          primaryKey: true,
          autoIncrement: true,
          comment: '功能模块ID'
        },
        key: {
          type: DataTypes.STRING(50),
          allowNull: false,
          unique: true,
          comment: '功能键值'
        },
        name: {
          type: DataTypes.STRING(100),
          allowNull: false,
          comment: '功能名称'
        },
        icon: {
          type: DataTypes.STRING(100),
          allowNull: true,
          comment: '图标'
        },
        bg_color: {
          type: DataTypes.STRING(20),
          allowNull: true,
          comment: '背景色'
        },
        icon_color: {
          type: DataTypes.STRING(20),
          allowNull: true,
          comment: '图标颜色'
        },
        route: {
          type: DataTypes.STRING(200),
          allowNull: true,
          comment: '路由地址'
        },
        sort: {
          type: DataTypes.INTEGER,
          defaultValue: 0,
          comment: '排序'
        },
        enabled: {
          type: DataTypes.BOOLEAN,
          defaultValue: true,
          comment: '是否启用'
        },
        created_at: {
          type: DataTypes.DATE,
          allowNull: false,
          defaultValue: Sequelize.NOW
        },
        updated_at: {
          type: DataTypes.DATE,
          allowNull: false,
          defaultValue: Sequelize.NOW
        }
      }, {
        transaction,
        charset: 'utf8mb4',
        collate: 'utf8mb4_unicode_ci',
        comment: '功能模块表'
      });

      // 3. 活动表
      await queryInterface.createTable('activities', {
        id: {
          type: DataTypes.INTEGER,
          primaryKey: true,
          autoIncrement: true,
          comment: '活动ID'
        },
        title: {
          type: DataTypes.STRING(200),
          allowNull: false,
          comment: '活动标题'
        },
        description: {
          type: DataTypes.TEXT,
          allowNull: true,
          comment: '活动描述'
        },
        poster: {
          type: DataTypes.STRING(500),
          allowNull: true,
          comment: '活动海报'
        },
        start_time: {
          type: DataTypes.DATE,
          allowNull: false,
          comment: '开始时间'
        },
        end_time: {
          type: DataTypes.DATE,
          allowNull: false,
          comment: '结束时间'
        },
        status: {
          type: DataTypes.ENUM('active', 'inactive', 'ended'),
          defaultValue: 'active',
          comment: '活动状态'
        },
        participant_count: {
          type: DataTypes.INTEGER,
          defaultValue: 0,
          comment: '参与人数'
        },
        rules: {
          type: DataTypes.TEXT,
          allowNull: true,
          comment: '活动规则'
        },
        prizes: {
          type: DataTypes.JSON,
          allowNull: true,
          comment: '奖品信息'
        },
        created_at: {
          type: DataTypes.DATE,
          allowNull: false,
          defaultValue: Sequelize.NOW
        },
        updated_at: {
          type: DataTypes.DATE,
          allowNull: false,
          defaultValue: Sequelize.NOW
        }
      }, {
        transaction,
        charset: 'utf8mb4',
        collate: 'utf8mb4_unicode_ci',
        comment: '活动表'
      });

      // 4. 检测文本表
      await queryInterface.createTable('detection_texts', {
        id: {
          type: DataTypes.INTEGER,
          primaryKey: true,
          autoIncrement: true,
          comment: '检测文本ID'
        },
        title: {
          type: DataTypes.STRING(200),
          allowNull: false,
          comment: '文本标题'
        },
        content: {
          type: DataTypes.TEXT,
          allowNull: false,
          comment: '文本内容'
        },
        pinyin: {
          type: DataTypes.TEXT,
          allowNull: true,
          comment: '拼音标注'
        },
        level: {
          type: DataTypes.ENUM('easy', 'medium', 'hard'),
          defaultValue: 'easy',
          comment: '难度级别'
        },
        category: {
          type: DataTypes.STRING(50),
          allowNull: true,
          comment: '文本分类'
        },
        duration: {
          type: DataTypes.INTEGER,
          allowNull: true,
          comment: '建议朗读时长(秒)'
        },
        standard_audio: {
          type: DataTypes.STRING(500),
          allowNull: true,
          comment: '标准音频URL'
        },
        tags: {
          type: DataTypes.JSON,
          allowNull: true,
          comment: '标签'
        },
        usage_count: {
          type: DataTypes.INTEGER,
          defaultValue: 0,
          comment: '使用次数'
        },
        is_active: {
          type: DataTypes.BOOLEAN,
          defaultValue: true,
          comment: '是否启用'
        },
        created_at: {
          type: DataTypes.DATE,
          allowNull: false,
          defaultValue: Sequelize.NOW
        },
        updated_at: {
          type: DataTypes.DATE,
          allowNull: false,
          defaultValue: Sequelize.NOW
        }
      }, {
        transaction,
        charset: 'utf8mb4',
        collate: 'utf8mb4_unicode_ci',
        comment: '检测文本表'
      });

      // 5. 游戏内容表
      await queryInterface.createTable('game_contents', {
        id: {
          type: DataTypes.INTEGER,
          primaryKey: true,
          autoIncrement: true,
          comment: '游戏内容ID'
        },
        image: {
          type: DataTypes.STRING(500),
          allowNull: false,
          comment: '图片URL'
        },
        pinyin: {
          type: DataTypes.STRING(200),
          allowNull: false,
          comment: '拼音'
        },
        standard_text: {
          type: DataTypes.STRING(200),
          allowNull: false,
          comment: '标准文本'
        },
        level: {
          type: DataTypes.ENUM('easy', 'medium', 'hard'),
          defaultValue: 'easy',
          comment: '难度级别'
        },
        category: {
          type: DataTypes.STRING(50),
          allowNull: true,
          comment: '内容分类'
        },
        audio_url: {
          type: DataTypes.STRING(500),
          allowNull: true,
          comment: '标准音频URL'
        },
        tags: {
          type: DataTypes.JSON,
          allowNull: true,
          comment: '标签'
        },
        usage_count: {
          type: DataTypes.INTEGER,
          defaultValue: 0,
          comment: '使用次数'
        },
        is_active: {
          type: DataTypes.BOOLEAN,
          defaultValue: true,
          comment: '是否启用'
        },
        created_at: {
          type: DataTypes.DATE,
          allowNull: false,
          defaultValue: Sequelize.NOW
        },
        updated_at: {
          type: DataTypes.DATE,
          allowNull: false,
          defaultValue: Sequelize.NOW
        }
      }, {
        transaction,
        charset: 'utf8mb4',
        collate: 'utf8mb4_unicode_ci',
        comment: '游戏内容表'
      });

      console.log('✅ API相关数据库表创建完成');
      await transaction.commit();

    } catch (error) {
      await transaction.rollback();
      console.error('❌ 创建API表失败:', error);
      throw error;
    }
  },

  async down(queryInterface, Sequelize) {
    const transaction = await queryInterface.sequelize.transaction();
    
    try {
      console.log('🗑️  开始删除API相关数据库表...');

      await queryInterface.dropTable('game_contents', { transaction });
      await queryInterface.dropTable('detection_texts', { transaction });
      await queryInterface.dropTable('activities', { transaction });
      await queryInterface.dropTable('function_modules', { transaction });
      await queryInterface.dropTable('banners', { transaction });

      console.log('✅ API相关数据库表删除完成');
      await transaction.commit();

    } catch (error) {
      await transaction.rollback();
      console.error('❌ 删除API表失败:', error);
      throw error;
    }
  }
};
